#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class RaovatDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new Raovat instance filled with the DataReader's current record data 
	     /// </summary>
	     private Raovat GetRaovatFromReader(IDataReader dr)
	     {
		     Raovat obj = new Raovat();
		     obj.RaovatID = (dr["RaovatID"] is DBNull) ? Int32.MinValue :  (Int32)dr["RaovatID"];
		     obj.UserID = (dr["UserID"] is DBNull) ? String.Empty :  Convert.ToString(dr["UserID"]);
		     obj.CateID = (dr["CateID"] is DBNull) ? Int32.MinValue :  (Int32)dr["CateID"];
		     obj.LocationID = (dr["LocationID"] is DBNull) ? Int32.MinValue :  (Int32)dr["LocationID"];
		     obj.Title = (dr["Title"] is DBNull) ? String.Empty :  (String)dr["Title"];
		     obj.Description = (dr["Description"] is DBNull) ? String.Empty :  (String)dr["Description"];
		     obj.FullName = (dr["FullName"] is DBNull) ? String.Empty :  (String)dr["FullName"];
		     obj.Address = (dr["Address"] is DBNull) ? String.Empty :  (String)dr["Address"];
		     obj.Email = (dr["Email"] is DBNull) ? String.Empty :  (String)dr["Email"];
		     obj.Phone = (dr["Phone"] is DBNull) ? String.Empty :  (String)dr["Phone"];
		     obj.Mobile = (dr["Mobile"] is DBNull) ? String.Empty :  (String)dr["Mobile"];
		     obj.Fax = (dr["Fax"] is DBNull) ? String.Empty :  (String)dr["Fax"];
		     obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.Now :  (DateTime)dr["AddedDate"];
		     obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty :  (String)dr["AddedBy"];
		     obj.DisplayOrder = (dr["DisplayOrder"] is DBNull) ? Int32.MinValue :  (Int32)dr["DisplayOrder"];
             obj.TotalViews = (dr["TotalViews"] is DBNull) ? 1 : (Int32)dr["TotalViews"];
		     obj.IsActive = (dr["IsActive"] is DBNull) ? false :  (Boolean)dr["IsActive"];
		     return obj;
	     }
	     #endregion


         public Boolean UpdateVisitor(Int32 RaovatID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("Raovat_UpdateVisitor", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = RaovatID;
                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


	     /// <summary>
	     /// Returns a collection of Raovat with the data read from the input DataReader
	     /// </summary>
	     private List<Raovat> GetRaovatCollectionFromReader(IDataReader dr)
	     {
		     List<Raovat> obj = new List<Raovat>();
		     while(dr.Read())
			     obj.Add(GetRaovatFromReader(dr));
		     return obj;
	     }

         public List<Raovat> GetRaovatDakiemduyet()
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("Raovat_GetRaovatDakiemduyet", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetRaovatCollectionFromReader(dr);
             }
         }


         //Search Raovat
         public List<Raovat> Search(int CateID, int LocationID, string Keyword)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 string sql = "select Distinct Raovat.* from [Raovat],Job_Category Where (RaovatID > 0) ";
                 if (CateID > 0) // 25 la loai Raovat trong table category
                 {
                     sql += "And (Job_Category.CateID = " + CateID + " Or Job_Category.ParentCateID = " + CateID + ") ";
                     sql += "And (Raovat.CateID = Job_Category.CateID) ";
                 }
                 if(LocationID > 0)
                     sql += "And (Raovat.LocationID = "+ LocationID +") ";

                 if (Keyword.Length > 0)
                     sql = sql + " And((Title Like N'%" + Keyword + "%') Or (Description Like N'%" + Keyword + "%'))";

                 sql += "And (Raovat.IsActive = 1) ";

                 sql = sql + " Order By DisplayOrder Asc, AddedDate Desc";

                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetRaovatCollectionFromReader(dr);
             }
         }

         public Boolean Kiemduyet(Int32 RaovatID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("Raovat_Kiemduyet", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = RaovatID;
                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


	     public int Insert(Raovat obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = obj.LocationID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = obj.Description;
		     myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
		     myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
		     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
		     myCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = obj.Phone;
		     myCommand.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = obj.Mobile;
		     myCommand.Parameters.Add("@Fax", SqlDbType.VarChar).Value = obj.Fax;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
		     myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
		     myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@RaovatID"].Value; 
		     }
	     }

	     public bool Update(Raovat obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_Update", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = obj.RaovatID;
		     myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = obj.CateID;
		     myCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = obj.LocationID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@Description", SqlDbType.NVarChar).Value = obj.Description;
		     myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
		     myCommand.Parameters.Add("@Address", SqlDbType.NVarChar).Value = obj.Address;
		     myCommand.Parameters.Add("@Email", SqlDbType.VarChar).Value = obj.Email;
		     myCommand.Parameters.Add("@Phone", SqlDbType.VarChar).Value = obj.Phone;
		     myCommand.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = obj.Mobile;
		     myCommand.Parameters.Add("@Fax", SqlDbType.VarChar).Value = obj.Fax;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
		     myCommand.Parameters.Add("@DisplayOrder", SqlDbType.Int).Value = obj.DisplayOrder;
		     myCommand.Parameters.Add("@IsActive", SqlDbType.Bit).Value = obj.IsActive;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     public bool Delete(Int32 RaovatID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = RaovatID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the Raovat with the specified ID
	     /// </summary>
	     public Raovat GetRaovatByID(Int32 RaovatID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetRaovatByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@RaovatID", SqlDbType.Int).Value = RaovatID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetRaovatFromReader(dr) : null);
		     }
	     }

	     //Get All Raovat
	     public List<Raovat> GetAllRaovat(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetAllRaovat", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetRaovatCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  Raovat
	     public int GetCountRaovat()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetCountRaovat", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	     // Get RaovatByCateID
	     public List<Raovat> GetRaovatByCateID(Int32 CateID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetRaovatByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetRaovatCollectionFromReader(dr);
		     }
	     }

	     // Get RaovatByLocationID
	     public List<Raovat> GetRaovatByLocationID(Int32 LocationID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetRaovatByLocationID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = LocationID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetRaovatCollectionFromReader(dr);
		     }
	     }

	     // Get RaovatCountByCateID
	     public int GetRaovatCountByCateID(Int32 CateID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetRaovatCountByCateID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@CateID", SqlDbType.Int).Value = CateID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }


	     // Get RaovatCountByLocationID
	     public int GetRaovatCountByLocationID(Int32 LocationID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("Raovat_GetRaovatCountByLocationID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@LocationID", SqlDbType.Int).Value = LocationID;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	 }
}
